﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Configuration;
using System.Data.SQLite;

namespace WindowsFormsApplication1
{
    public partial class Form3 : Form
    {
        public Form3()
        {
            InitializeComponent();
        }

        SQLiteConnection conn;
        SQLiteDataAdapter sda;
        DataSet ds;

        private void Form3_Load(object sender, EventArgs e)
        {
            string conString = ConfigurationManager.ConnectionStrings["myconn"].ConnectionString;
            conn = new SQLiteConnection(conString);
        }

        private void button1_Click(object sender, EventArgs e)
        {
            SQLiteCommand cmd = new SQLiteCommand("select * from test_customer", conn);
            sda = new SQLiteDataAdapter();
            sda.SelectCommand = cmd;
            ds = new DataSet();
            sda.Fill(ds, "test2");
            dataGridView1.DataSource = ds.Tables[0];
        }

        private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            textBox1.Text = dataGridView1.Rows[e.RowIndex].Cells[0].Value.ToString();
            textBox2.Text = dataGridView1.Rows[e.RowIndex].Cells[1].Value.ToString();
            textBox3.Text = dataGridView1.Rows[e.RowIndex].Cells[2].Value.ToString();
            dateTimePicker1.Text = dataGridView1.Rows[e.RowIndex].Cells[3].Value.ToString();
            textBox5.Text = dataGridView1.Rows[e.RowIndex].Cells[4].Value.ToString();
            textBox6.Text = dataGridView1.Rows[e.RowIndex].Cells[5].Value.ToString();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            string cid = textBox1.Text.ToString();
            string name = textBox2.Text;
            string sex = textBox3.Text;
            string birthbay = dateTimePicker1.Text;
            string address = textBox5.Text;
            string phonenumber = textBox6.Text.ToString();
            string instring = "insert into test_customer (cid,name,sex,birthday,address,phonenumber) values (" + cid + ",'" + name + "','" + sex + "',date('" + birthbay + "'),'" + address + "'," + phonenumber + " )";
            conn.Open();
            SQLiteCommand cmd = new SQLiteCommand(instring, conn);
            cmd.ExecuteNonQuery();
            conn.Close();
            ds.Clear();
            sda.Fill(ds, "test2");
        }

        private void button3_Click(object sender, EventArgs e)
        {
            string cid = textBox1.Text.ToString();
            string name = textBox2.Text;
            string sex = textBox3.Text;
            string birthbay = dateTimePicker1.Text;
            string address = textBox5.Text;
            string phonenumber = textBox6.Text.ToString();
            string upstring = "update test_customer set cid =" + cid + ", name ='" + name + "', sex ='" + sex + "', birthday =date('" + birthbay + "'), address ='" + address + "', phonenumber =" + phonenumber + " where cid = " + cid;
            conn.Open();
            SQLiteCommand cmd = new SQLiteCommand(upstring, conn);
            cmd.ExecuteNonQuery();
            conn.Close();
            ds.Clear();
            sda.Fill(ds, "test2");
        }

        private void button4_Click(object sender, EventArgs e)
        {
            string cid = textBox1.Text.ToString();
            string destring = "delete from test_customer where cid =" + cid;
            conn.Open();
            SQLiteCommand cmd = new SQLiteCommand(destring, conn);
            cmd.ExecuteNonQuery();
            conn.Close();
            ds.Clear();
            sda.Fill(ds, "test2");
        }
    }
}
